Release 10.1A: OpenEdge Reporting:
Query/Results Administration and Development

Accessing and editing the current query

The following sections provide information about how to use the API shared variables and procedures to access and edit information associated with the current query in Results.

Determining the current view

Use the qbf–module shared variable in your feature and integration procedures to determine and set the current view for the current query in Results. The qbf–module character variable holds one of the characters listed in Table 3–8.

Table 3–8: View character codes 

Value	Current view
?	No current query or view
b	Browse view
f	Form view
r	Report view
l	Label view
e	Data Export view

When the qbf–module variable contains the unknown value (?), there is no current query or view in the Results application window.

When you set the qbf–module variable in a feature procedure, make sure the output parameter controlling the redisplay of Results is set to TRUE. Consider the following feature procedure example that is presented in Example 3–4.

Example 3–4: Feature procedure

DEFINE INPUT PARAMETER args AS CHARACTER NO-UNDO   DEFINE OUTPUT PARAMETER winState AS LOGICAL NO-UNDO INITIAL FALSE. {aderes/u-pvars.i} qbf-module = ?. winState = TRUE.

The last line of this feature procedure sets the winState output parameter to TRUE. This causes the Results window to redraw and display the changed module state when the feature procedure finishes executing.

Determining the name of the current query

The shared variable qbf-name contains the name of the current query in Results. If you set the qbf-name variable in a feature procedure to a query that does not exist in the current query directory, Results creates a new temporary query definition. To save the new query definition permanently in the current query directory, execute the FileSave feature from a feature procedure or choose Query Save while the query is active in Results.

Accessing the tables and selection criteria of the current query

Results provides API procedures that retrieve information about the tables in the current query.

The vgtbll.p API procedure retrieves a list of the tables referenced in the current query. Use the following syntax to call the vgtbll.p API procedure from a feature procedure:

RUN aderes/vgtbll.p (OUTPUT tableList).

The OUTPUT tableList parameter is a string value containing a comma-separated list of table or alias names referenced in the currently active query.

The vgtbli.p API procedure retrieves information about the join and selection criteria for a specified table referenced in the current query.

Use the following syntax to call the vgtbli.p API procedure from a feature procedure:

Syntax

RUN aderes/vgtbli.p (INPUT tableName, OUTPUT refTable, OUTPUT joinTable, OUTPUT joinInfo, OUTPUT whereInfo, OUTPUT whereAsk, OUTPUT includeCode, OUTPUT result).

The following list describes the parameters of the vgtbli.p API procedure:

Parameters for the vgtbli.p API procedure

The following list describes the parameters of the vgtbli.p API procedure:

INPUT tableName

A string value representing the name of a table or alias used in the current query.

OUTPUT refTable

If tableName is an alias, this parameter is a string value representing the name of the table associated with the alias. If tableName is an actual table name, this parameter returns the unknown value (?).

OUTPUT joinTable

A string value representing the name of a table to which the table specified with the first input parameter (tableName) is joined.

OUTPUT joinInfo

A string value containing the Progress 4GL statement relating the specified table with another table available in the currently active query.

OUTPUT whereInfo

A string value representing a selection expression in the WHERE clause of the query. This string value does not contain the keyword WHERE and contains only the selection criteria defined explicitly for the query. If this output parameter yields an empty string, there is no WHERE criteria for the corresponding table.

OUTPUT whereAsk

A string value representing the information used to prompt for a WHERE clause value at run time. This is the syntax for the string:

Syntax

/*dataType,dbField,operator:promptString*/ TRUE

The database field, the data type of the database field, the operator of the cirteria expression, and the prompt are listed.

OUTPUT includeCode

A string value containing Progress 4GL statements to execute immediately after each record access from the specified table in the current query.

OUTPUT result

A logical value that indicates whether the API procedure executed successfully.

Determining the query sort order

Use the qbf–sortby variable to retrieve and set the sorting information for the current query. The qbf–sortby variable contains BY clauses but does not contain the keyword BY. The list in the qbf–sortby variable has the following form:

Syntax

db-name.tbl-name.fld-name [ DESC ] [ , db-name.tbl-name.fld-name [ DESC ] ] ...

The fields are positioned in the list based on sort precedence, with the primary sort field listed first. The letters DESC specified after a field name in the list indicate that the sort order for the field is descending. If the field is an array field, the field name is followed by a number in square brackets (for example, [#], where # is the array element selected for ordering).

Determining the fields in the current query

Results places information about the fields of the current query into several different API shared variables. Many of the API shared variables that store information about the fields of the current query are arrays. In each of these field information arrays, there is one element per field in the current query. Results places information about each field into the field information arrays based on the order in which the fields are added to the current query. You can use a single index value to access information about a particular query field across these arrays. For example, an index value of 3 used with each field information array yields information about the third field added to the current query.

These are the shared variables and arrays that contain information about the fields of the current query:

qbf–rc# integer variable — Represents the total number of fields (database, calculated, and array elements) in the current query.
qbf–rcn variable — Identifies a character array that contains names for each field in the current query. The contents of an element of this array vary depending on the information stored in the corresponding element of the qbf–rcc array. If an element in the qbf–rcc array is blank, the corresponding element in qbf–rcn is a field name. Otherwise, the element contains a comma-separated list of information about a calculated field.

The format of the information list of a calculated field varies depending on the type of calculated field. The name of the calculated field is always the first entry in the list. By default, the calculated field name is qbf–###, where ### is a three-digit number representing the index of the current element in the qbf–rcn array. You can rename calculated fields using the Rename button in the Properties dialog box. The ENTRY(1,qbf–rcn[n]) function always returns the field name from an element of the qbf–rcn variable. Table 3–9 describes the format of the information list for each type of calculated field.

qbf–rcc variable — Identifies a character array that is used to store information for each calculated field and stacked array in the current query. (Stacked arrays display the elements in a column within a single field on the display.) If the first character is blank, the associated field is not a calculated field or a stacked array. For each calculated field in the qbf–rcc variable, the corresponding element in qbf–rcn contains information about the expression used to calculate the field.

Table 3–9 describes the symbols used in an element of the qbf–rcc array to represent the different types of calculated fields.

Table 3–9: Calculated field symbols

First character	Description	Corresponding qbf–rcn value format
r	Running total.	qFld, tFld,where qFld is the name of the query field. tFld is the name of the field used to calculate the running total.
p	Percent of total.	qFld, pFld, where qFld is the name of the query field. pFld is the name of the field used to calculate the percent of total.
c	Counter. The c is followed by one of the following symbols: <, >, or *, where The < symbol signifies that the count is based on the outer-most section of the report. The > symbol signifies that the count is based on the inner-most section of the report. The * symbol signifies that the count is based on all sections of the report.	qFld, sVal, iVal, where qFld is the name of the query field. sVal is the starting value. Val is the incremental value used in the count operation.
e	Stacked array. The e is followed by the extent of the array (for example, e12 for customer.mnth–sales[ ]).	qFld, aFld, where qFld is the name of the query field. aFld is the array field.w.
s	String calculated field. The s is followed by a comma-separated list of the names of the fields used in the calculation.	qFld, expr, where qFld is the name of the query field. expr is the expression used to calculate the field value.
n	Numeric calculated field. The n is followed by a comma-separated list of the names of the fields used in the calculation.	qFld, expr, where qFld is the name of the query field. expr is the expression used to calculate the field value.
l	Logical calculated field. The l is followed by a comma-separated list of the names of the fields used in the calculation.	qFld, expr, where qFld is the name of the query field. expr is the expression used to calculate the field value.
d	Date calculated field. The d is followed by a comma-separated list of the names of the fields used in the calculation.	qFld, expr, where qFld is the name of the query field. expr is the expression used to calculate the field value.
x	Lookup field.	qFld, sTbl, sFld, mTbl, mFld, dFld, eStr, where qFld is the name of the query field. sTbl is the name of the source table. sFld is the name of the source field containing the lookup values. mTbl is the name of the match table. mFld is the name of the field in the match table containing values used for comparison in the lookup operation. dFld is the name of the field in the match table containing values to display for successful matches. eStr is the error string to display if there is no valid match.

Note: For calculated field types s, n, l, and d, ENTRY(1,qbf–rcn[n]) is the column name and SUBSTRING(qbf–rcn,INDEX(qbf–rcn[n],",") + 1) contains the expression. You cannot use ENTRY(2,...) to retrieve the expression, since it might have embedded commas.
The qbf–rcg variable is a character array containing totals information for each field in the current query. Elements that contain an empty string have no totals information for the corresponding query field. If an element contains an ampersand (&), repeating values are hidden for the associated field.

An element of the qbf–rcg variable can also contain one or more aggregate strings that consist of a leading letter followed by an integer or 0. An aggregate string must have one of the forms listed in Table 3–10.

Table 3–10: Aggregate string 

Character	Description
tn	Total
cn	Count
an	Average
nn	Minimum
xn	Maximum

If n is 0, the total accumulation for the associated field is placed on the bottom line (summary line) of the report view of the current query. If n is an integer, it identifies an element in the qbf–sortby variable containing the name of a query field that triggers the accumulation.

An aggregate string can also contain a dollar sign ($), which indicates that the accumulate string is a totals-only string.

The qbf–rcl variable is a character array containing labels for each field in the current query. Stacked column labels are supported. In stacked column labels, an exclamation point (!) breaks column labels into more than one level.
The qbf–rcf variable is a character array containing format strings for each field in the current query. All OpenEdge Data Dictionary formats are supported.

The qbf–rcp variable is a character array containing layout parameters related to the report, form, and label views for a field in the current query. Each element of the qbf–rcp variable has the following syntax:

Syntax

rCol, rRow, fCol, fRow, lCol, lRow

Table 3–11 are the entries contained in each element of the qbf–rcp variable:

Table 3–11: Elements of the qbf-rcp variable 

Variable	Description
rCol	An integer representing the column position in characters for the current field from the left side of the report view.
rRow	An integer representing the row position in lines for the current field from the top of the Report view.
fCol	An integer representing the column position in characters for the current field from the left side of the Form view.
fRow	An integer representing the row position in lines for the current field from the top of the Form view.
lCol	An integer representing the column position in characters for the current field from the left side of the Label view.
lRow	An integer representing the row position in lines for the current field from the top of the Label view.

Note: Do not set an element of the qbf–rcp variable to the empty string. Instead, set the element to a character string containing five commas (“,,,,,”).
The qbf–rcw variable is an integer array containing character widths for each query field in the report view. The width of each field includes format, label widths, and stacked labels. This variable is for information purposes only. Results calculates the character width of a field automatically when the query displays.
The qbf–rct variable is an integer array containing a data type identifier for each field in the current query. The data type identifier is an integer value between 1 and 7 (inclusive) that maps to a list element contained in the qbf–dtype variable.

The qbf–dtype character variable containing a comma-separated list of data types. The default value of this variable is a character array:

"character,date,logical,integer,decimal,raw,recid"

Use this variable with the qbf–rct variable to determine the data type of a field in the current query.

Determining the report format of the current query

The vgrpti.p API procedure retrieves report format information associated with the current query. Use the following syntax to call the vgrpti.p API procedure from a feature procedure:

Syntax

RUN aderes/vgrpti.p (OUTPUT leftOrigin, OUTPUT topOrigin, OUTPUT columnSpace, OUTPUT lineSpace, OUTPUT pageLines, OUTPUT headSpace, OUTPUT footSpace, OUTPUT pageEject, OUTPUT sumReport).

Parameters of the vgrpti.p API procedure

The following list describes the parameters of the vgrpti.p API procedure:

OUTPUT leftOrigin

An integer representing left margin setting, in characters, from the output origin (page or display window).

OUTPUT topOrigin

An integer representing top margin setting, in characters, from the output origin (page or display window).

OUTPUT columnSpace

An integer representing the number of character units between fields on the report.

OUTPUT lineSpace

An integer representing the number of empty lines between text lines on the report.

OUTPUT pageLines

An integer representing the maximum number of lines allowed on a printed page of the report.

OUTPUT headSpace

An integer representing the number of lines between the report body and the report header.

OUTPUT footSpace

An integer representing the number of lines between the report body and the report footer.

OUTPUT pageEject

A string value containing the name of the report field used for page ejects.

OUTPUT sumReport

A logical value that specifies whether the report is a summary report.

The report view does not have to be active in Results in order to run the vgrpti.p API procedure.

Setting table information for the current query

The vstbli.p API procedure sets table information associated with the current query. Use the following syntax to call the vstbli.p API procedure from a feature procedure:

Syntax

RUN aderes/vstbli.p (INPUT tableName, INPUT joinTable, INPUT joinInfo, INPUT whereInfo, INPUT whereAsk, INPUT includedCode, OUTPUT result).

Parameters of the vstbli.p API procedure

The following list describes the parameters of the vstbli.p API procedure:

INPUT tableName

A string value representing the name of a table or alias used in the current query. The table name must be fully qualified (for example, db.table.name).

INPUT joinTable

A string value representing the name of the table to which tableName is joined, if any.

INPUT joinInfo

A string value containing the Progress 4GL code defining the join relationship between joinTable and tableName.

INPUT whereInfo

A string value representing a selection expression in the WHERE clause of the query. This string value does not contain the keyword WHERE and contains only the selection criteria defined explicitly for the query.

INPUT whereAsk

A string value representing the information used to prompt for a WHERE clause value at run time. This is the syntax for the string value:

Syntax

/*dataType,Field,operator:promptString*/ TRUE

The field, the data type of the field, the operator of the criteria expression, and prompt string are listed.

INPUT includedCode

A string value containing Progress 4GL statements to execute immediately after each record access from the specified table in the current query.

OUTPUT result

A logical value that indicates whether the API procedure executed successfully.

Setting the list of tables for the current query

The vstbll.p API procedure sets the list of tables associated with the current query. Use the following syntax to call the vstbll.p API procedure from a feature procedure:

Syntax

RUN aderes/vstbll.p (INPUT tableList, OUTPUT result).

Parameters of the vstbll.p API procedure

The following list describes the parameters of the vstbll.p API procedure:

INPUT tableList

A string value containing a list of tables to be used in the current query. The table names must be fully qualified (for example, db.table.name).

OUTPUT result

A logical value that indicates whether the API procedure executed successfully.

Setting the export format for the current query

The vsexpi.p API procedure sets the export format for the current query. Use the following syntax to call the vsexpi.p API procedure from a feature procedure:

Syntax

RUN aderes/vsexpi.p (INPUT newFormat, OUTPUT result, OUTPUT oldFormat).

Parameters of th vsexpi.p API procedure

The following list describes the parameters of the vsexpi.p API procedure:

INPUT newFormat

A string value representing the name of the export format to make active for the current query. If the format name is not found in the list of existing formats, it is not set.

OUTPUT result

A logical value that indicates whether the API procedure executed successfully.

OUTPUT oldFormat

A string value representing the name of the old (prior to calling this API) export format. This information can be used to restore the export format to its original state at the end of the feature procedure.

Setting the report format for the current query

The vsrpti.p API procedure sets the report (page) format for the current query. Use the following syntax to call the vsrpti.p API procedure from a feature procedure:

Syntax

RUN aderes/vsrpti.p (INPUT newFormat, OUTPUT result, OUTPUT oldFormat).

Parameters of the vsrpti.p API procedure

The following list describes the parameters of the vsrpti.p API procedure:

INPUT newFormat

A string value representing the name of the page format to make active for the current query. If the format name is not found in the list of existing formats, it is not set.

OUTPUT result

A logical value that signals whether or not the API procedure executed successfully.

OUTPUT oldFormat

A string value representing the name of the old (prior to calling this API) page format. This information can be used to restore the page format to its original state at the end of the feature procedure.

Setting the label format for the current query

The vslbli.p API procedure sets the label format for the current query. Use the following syntax to call the vslbli.p API procedure from a feature procedure:

Syntax

RUN aderes/vslbli.p (INPUT newFormat, OUTPUT result, OUTPUT oldFormat).

Parameters of the vslbli.p API procedure

The following list describes the parameters of the vslbli.p API procedure:

INPUT newFormat

A string value representing the name of the label format to make active for the current query. If the format name is not found in the list of existing formats, it is not set.

OUTPUT result

A logical value that indicates whether the API procedure executed successfully.

OUTPUT oldFormat

A string value representing the name of the old (prior to calling this API) label format. This information can be used to restore the label format to its original state at the end of the feature procedure.